<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_M_AREA_AD extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table  = "M_AREA_AD";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey   = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	 protected $_order = "area_cd";
	 
	 protected $_cols       =  array(
		"area_cd"           => array(AOKI_VARTYPE_STR),
		"area_name"         => array(AOKI_VARTYPE_STR),
		"publish_area_cd"   => array(AOKI_VARTYPE_STR),
		"publish_area_name" => array(AOKI_VARTYPE_STR),
		"hand_cd"           => array(AOKI_VARTYPE_STR),
		"hand_name"         => array(AOKI_VARTYPE_STR),
		"media_cd"          => array(AOKI_VARTYPE_STR),
		"media_name"        => array(AOKI_VARTYPE_STR),
		"area_nums"         => array(AOKI_VARTYPE_STR),
		"begin_date"        => array(AOKI_VARTYPE_STR),
		"user_flag"         => array(AOKI_VARTYPE_STR),
	 );
	
	 protected $_checks     =  array(
		"area_cd"         => array( "EXIST" ),
		"publish_area_name" => array( "EXIST" ),
		"hand_name"         => array( "EXIST" ),
		"media_name"        => array( "EXIST" ),
		"area_nums"         => array( "EXIST", "NUM" ),
		"week_cd"        	=> array( "EXIST" ),
	 );
	 
	 //maxlength 
	 protected $_maxlens  = array( );
	
	 public function search($where, &$page = NULL,  $shop_name){
	 	$jointable       = " select a.area_ad_shop_id,count(*) as shop_count,sum(a.shop_nums) as shop_nums,max(a.u_time) as au_time,max(a.u_user) as au_user  from M_AREA_AD_SHOP a "; 
	 	
	 	
		if (!empty( $shop_name )) {
			$jointableWhere  = " inner join M_SHOP b on a.shop_cd = b.shop_cd WHERE b.shop_name like '%{$shop_name}%' group by a.area_ad_shop_id ";
			$fromtable = " FROM {$this->_table}  a inner  join ({$jointable} {$jointableWhere}) b on a.id = b.area_ad_shop_id " ;
		} else {
			$jointableWhere  = "  inner join M_SHOP b on a.shop_cd = b.shop_cd group by a.area_ad_shop_id ";
			$fromtable = " FROM {$this->_table}  a left  join ({$jointable} {$jointableWhere}) b on a.id = b.area_ad_shop_id " ;
		}
		$fromtable =  $fromtable. "inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd ";
		
		$sql = "SELECT a.*,m.flier_date,m.year4,m.week_cd, b.shop_count, b.shop_nums,b.au_time, (select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name,(select  user_name FROM M_LOGIN where id  = b.au_user) as m_auser_name " .$fromtable;
		
		$sqlcnt = "SELECT count(*) as cnt ".$fromtable;
		$orderby = "a.matrix_cd,a.area_cd";

		return  parent::search($where ,$page ,$sql, $sqlcnt, $orderby);
	}
	
	
	 public function searchcsv($where, $shop_name, $flg = NULL ){
	 	$whereshopname = "";
	 	 if  (!empty( $shop_name )) {
	 	 	$whereshopname =   "AND sp.shop_name like '%{$shop_name}%' ";
	 	 }
	 	 if (empty($flg)) {
	 	  	$sql = "SELECT  b.*,a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd, sp.shop_name," .
	 		"(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name " ;
	 	 	$sql = $sql."  FROM {$this->_table}  a inner  join M_AREA_AD_SHOP b on a.id = b.area_ad_shop_id " .
	 		" inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}".
	 		" inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }elseif ($flg == "b") {
	 		$sql = "SELECT  a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd" ;
	 		$sql = $sql."  FROM {$this->_table}  a left join M_AREA_AD_SHOP b on a.id = b.area_ad_shop_id " ;
	 		if  (!empty( $shop_name )) {
	 			$sql = $sql." inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}";
	 		}
	 		$sql = $sql." inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }elseif ($flg == "d") {
	 		$sql = "SELECT  b.*,a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd, sp.shop_name," .
	 		"(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name ";
	 	 	$sql = $sql."  FROM {$this->_table}  a inner  join M_AREA_AD_SHOP b on a.id = b.area_ad_shop_id " .
	 		" inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}".
	 		" inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }
	 	 
		$pagenull = NULL;
	 	if  ($flg == "b") {
	 		$sql = $sql." where ".implode(' AND ', $where);
	 		$sql = $sql." group by a.id ";
	 		return  parent::search(array() ,$pagenull ,$sql);
	 	}else {
	 		//$sql = $sql." group by a.id ";
	 		return  parent::search($where ,$pagenull ,$sql);
	 	}
	 	
		
		
	}
	/**
	 *
	 * 単一レコードを取得する
	 *
	 * @access    public
	 * @param     mixed     $id   主キーの値
	 * @return    mixed     取得に失敗した場合にFALSE, 成功した場合にレコードの内容(array)
	 */
	public function getByAreaAdShopCd($value) {
	    $fromtable = " FROM M_AREA_AD_SHOP " ;
		$where     = " WHERE a.area_ad_shop_id = '{$value}'" ;
		
		$sql = "SELECT * " .$fromtable.$where;
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getShopCount($value) {
		$sql = "SELECT count(*)  as cnt,sum(shop_nums) as nums FROM M_AREA_AD_SHOP WHERE area_ad_shop_id = '{$value}'";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return $this->_db->fetch();
			
		}else{
			return  null;
		}
	}
	
	public function updateShopNums($area_cd, $nums) {
		
		$sql = "update  M_AREA_AD_SHOP set shop_nums  = round( shop_per * {$nums} /100)   WHERE area_ad_shop_id = '{$area_cd}'  " ;
		return $this->exec_sql($sql) ;
	}
	
}

?>